package com.jketing.spring.mvc.office.view;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import com.jketing.entity.ams.work.Repetition;
import com.jketing.entity.ams.work.Variety;
/**
 * ShiroRealm shiro认证和授权Realm
 * 
 * @author  李荣华
 * @version v1.0
 * @date    2018-10-19
 * @since   poi 4.0
 */
public class MaizeBillExcelView extends AbstractXlsView{
	
	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		String filename = sdf.format(date);
		HSSFSheet sheet;
		response.setContentType("application/octet-stream");
		response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
		
		List<String> titles = (List<String>) model.get("titles");
		
		HSSFWorkbook book = (HSSFWorkbook) workbook;
		sheet = book.createSheet("sheet1");
		CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,titles.size()-1);
		sheet.addMergedRegion(callRangeAddress);
		HSSFCellStyle titleStyle = book.createCellStyle(); //标题样式
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		HSSFFont font = book.createFont();//字体
		font.setBold(true);
		font.setFontHeightInPoints((short)24);
		titleStyle.setFont(font);
		
		HSSFCellStyle headerStyle = book.createCellStyle(); //表头样式
		headerStyle.setAlignment(HorizontalAlignment.CENTER);
		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		font = book.createFont();	
		font.setBold(true);
		font.setFontHeightInPoints((short)14);
		headerStyle.setFont(font);
		
		short height=42*20;
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
        //加载单元格样式
        cell.setCellStyle(titleStyle);
        cell.setCellValue("玉米试验考种表");
        sheet.getRow(0).setHeight(height);
        
        row = sheet.createRow(1);
        HSSFCellStyle cellStyle = book.createCellStyle();       
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		for(int i=0; i<titles.size(); i++){ //设置表头
			String header = titles.get(i);
			row.setRowStyle(headerStyle);
			cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(new HSSFRichTextString(header));  
		}
		height=48*20;
		sheet.getRow(1).setHeight(height);
		
		HSSFCellStyle contentStyle = book.createCellStyle(); //内容样式
		contentStyle.setAlignment(HorizontalAlignment.CENTER);
		contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		height = 20*20;
		List<Variety> list = (List<Variety>) model.get("varList");
		int varCount = list.size();
		int rowNum = 2;
		for(int i=0; i<varCount; i++){
			Variety v = list.get(i);			
			int count = 0;
			Iterator<Repetition> it = v.getRepetitions().iterator();
			while (it.hasNext()) {
				Repetition r = it.next();
				HSSFRow rows = sheet.createRow(rowNum + count); 
				rows.setRowStyle(contentStyle);
				rows.setHeight(height);
				count++;
				cell = rows.createCell(0);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getName());
				cell = rows.createCell(1);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(count);
				cell = rows.createCell(2);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSpikeLength());  
				cell = rows.createCell(3);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSpikeWide());  
				cell = rows.createCell(4);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSpikeRowNumber());  
				cell = rows.createCell(5);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getRowGrainNumber());  
				cell = rows.createCell(6);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getGrainColor());  
				cell = rows.createCell(7);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getGrainType());  
				cell = rows.createCell(8);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSpikeType());  
				cell = rows.createCell(9);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getAxialColour());  
				cell = rows.createCell(10);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSpikeRot());
				cell = rows.createCell(11);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getGrainRot());
				cell = rows.createCell(12);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getAlopeciaAcuity());
				cell = rows.createCell(13);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getThousandGrainWeight());
				cell = rows.createCell(14);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getFreshPackageWeight());
				cell = rows.createCell(15);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getDryPackageWeight());
				cell = rows.createCell(16);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getDryGrainWeight());
				cell = rows.createCell(17);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSeedRate());
				cell = rows.createCell(18);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getSeedYield());
				cell = rows.createCell(19);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getPlotFreshWeight());
				cell = rows.createCell(20);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getPlotYield());
				cell = rows.createCell(21);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(r.getYieldPerMu());
			}
			rowNum = rowNum + count;
			if(count>1) {
				callRangeAddress = new CellRangeAddress(rowNum-count,rowNum,0,0);
				sheet.addMergedRegion(callRangeAddress);
				HSSFRow rows = sheet.createRow(rowNum); 
				rows.setRowStyle(contentStyle);
				rows.setHeight(height);
				cell = rows.createCell(1);
				cell.setCellStyle(cellStyle);
				cell.setCellValue("平均");
				cell = rows.createCell(2);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSpikeLength());  
				cell = rows.createCell(3);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSpikeWide());  
				cell = rows.createCell(4);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSpikeRowNumber());  
				cell = rows.createCell(5);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getRowGrainNumber());  
				cell = rows.createCell(6);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getGrainColor());  
				cell = rows.createCell(7);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getGrainType());  
				cell = rows.createCell(8);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSpikeType());  
				cell = rows.createCell(9);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getAxialColour());  
				cell = rows.createCell(10);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSpikeRot());
				cell = rows.createCell(11);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getGrainRot());
				cell = rows.createCell(12);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getAlopeciaAcuity());
				cell = rows.createCell(13);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getThousandGrainWeight());
				cell = rows.createCell(14);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getFreshPackageWeight());
				cell = rows.createCell(15);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getDryPackageWeight());
				cell = rows.createCell(16);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getDryGrainWeight());
				cell = rows.createCell(17);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSeedRate());
				cell = rows.createCell(18);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getSeedYield());
				cell = rows.createCell(19);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getPlotFreshWeight());
				cell = rows.createCell(20);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getPlotYield());
				cell = rows.createCell(21);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(v.getYieldPerMu());
				rowNum++;
			}
		}
	}
}
